<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
use PHPExcel_Cell_DataType;
class Suoshu extends Common
{
    //销售导出
    public function xiaoshoudc($cid){
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
           
            $where="sdAgency_id in ({$str}) ";
            $whereti="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";
            $whereti="1<>1 ";
        }
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->order("starttime")->select();
        $nian=db("cg_cainian")->where("id=$cid")->value("title");
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
           
            $where="sdAgency_id in ({$str}) ";
            $whereti="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";
            $whereti="1<>1 ";
        }
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->order("starttime")->select();
        $this->assign("kaohe1",$kaohe1);
        //发运
        $fy=[];$zfy=0;
        //ti
        $ti=[];$zti=0;
        for($i=0;$i<count($kaohe1);$i++){

            //发运
            $tmpstart=date("Y-m-d",$kaohe1[$i]["starttime"]);
            $tmpend=date("Y-m-d",$kaohe1[$i]["endtime"]);
            $end=$kaohe1[$i]["endtime"]+3600*24;
            $fye=db("bx_fy")->where($where." and fyDate<='$tmpend' and fyDate>='{$tmpstart}'")->sum("fyZheMoney");
            if($fye){
                $fy[]=round($fye,2);
                $zfy+=round($fye,2);
            }else{
                $fy[]=0;
            }
            //ti
            $tie=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where($whereti." and t.ctime>={$kaohe1[$i]["starttime"]} and t.ctime<{$end} and t.status=7 and (t.status2=0 or t.status2 is null)")->field("td.yjsjtrfy,td.fb_shijitourufeiyong")->select();
            $tmpti=0;
            for($j=0;$j<count($tie);$j++){
                if($tie[$j]["fb_shijitourufeiyong"]){
                    $tmpti+=round($tie[$j]["fb_shijitourufeiyong"],2);
                }else{
                    $tmpti+=round($tie[$j]["yjsjtrfy"],2);
                }
            }
            $ti[]=$tmpti;
            $zti+=$tmpti;
        }
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("{$nian}所属所属销售及费用进度");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:F1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$nian}所属所属销售及费用进度");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:F2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);

        $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:F3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:F3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:F3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:F3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"第一季度");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"第二季度");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"第三季度");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"第四季度");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"合计");
        $h=3;
        $h++;

        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"TI费用",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$ti[0],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$ti[1],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$ti[2],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$ti[3],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$zti,PHPExcel_Cell_DataType::TYPE_STRING);
        $h++;
        
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"发运额",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$fy[0],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$fy[1],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$fy[2],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$fy[3],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$zfy,PHPExcel_Cell_DataType::TYPE_STRING);

         $h++;
        
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"回款额",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);

         $h++;
        
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"零售额",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);

        $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:F'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
           
           
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    } 
    //所属销售及费用进度
    public function xiaoshou($cid=null){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
           
            $where="sdAgency_id in ({$str}) ";
            $whereti="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";
            $whereti="1<>1 ";
        }
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->order("starttime")->select();
        $this->assign("kaohe1",$kaohe1);
        //发运
        $fy=[];$zfy=0;
        //ti
        $ti=[];$zti=0;
        for($i=0;$i<count($kaohe1);$i++){

            //发运
            $tmpstart=date("Y-m-d",$kaohe1[$i]["starttime"]);
            $tmpend=date("Y-m-d",$kaohe1[$i]["endtime"]);
            $end=$kaohe1[$i]["endtime"]+3600*24;
            $fye=db("bx_fy")->where($where." and fyDate<='$tmpend' and fyDate>='{$tmpstart}'")->sum("fyZheMoney");
            if($fye){
                $fy[]=round($fye,2);
                $zfy+=round($fye,2);
            }else{
                $fy[]=0;
            }
            //ti
            $tie=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where($whereti." and t.ctime>={$kaohe1[$i]["starttime"]} and t.ctime<{$end} and t.status=7 and (t.status2=0 or t.status2 is null)")->field("td.yjsjtrfy,td.fb_shijitourufeiyong")->select();
            $tmpti=0;
            for($j=0;$j<count($tie);$j++){
                if($tie[$j]["fb_shijitourufeiyong"]){
                    $tmpti+=round($tie[$j]["fb_shijitourufeiyong"],2);
                }else{
                    $tmpti+=round($tie[$j]["yjsjtrfy"],2);
                }
            }
            $ti[]=$tmpti;
            $zti+=$tmpti;
        }
        $this->assign("zfy",$zfy);
        $this->assign("fy",$fy);
        $this->assign("ti",$ti);
        $this->assign("zti",$zti);
        $this->assign("kaohec",count($kaohe1)+1);

        return $this->fetch();
    }
    public function jinzhandc(){
         $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";
        }
        $feiid=db("cg_fifeiyongkemu")->where("title like '%陈列%'")->value("id");
        if($feiid){
            $arr=db("cg_feiyongkemu3")->where("fifeiyongkemu<>$feiid")->field("id")->select();
            $tmparr=[];
            for($i=0;$i<count($arr);$i++){
                $tmparr[]=$arr[$i]["id"];
            }
            if($tmparr){
                $tmparr=implode(',',$tmparr);
                $where.="and td.feetype in ($tmparr) ";
            }else{
                //无费用科目
                $where.="and 1<>1 ";
            }
        }
        
      
        $having="count(t.id)>0 ";
        $ishave=0;
        if($ishave){
             $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->having($having)->order("t.id")
            ->field("t.sn,t.id,t.type_id,t.title")
            ->select();
        }else{
            $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->order("t.id")
            ->field("t.sn,t.id,t.type_id,t.title")
            ->select();
        }
       
       for($i=0;$i<count($apply);$i++){
            $apply[$i]["start"]=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->min("start");
            $apply[$i]["end"]=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->max("end");
            $net=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]} and net_id is not null")->group("net_id")->count();
            if($net){
                $apply[$i]["net"]=$net;
            }else{
                $apply[$i]["net"]=0;
            }
            if($apply[$i]["type_id"]==1){
                $apply[$i]["pro"]=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("pro_products p","td.tm_id=p.id")->group("p.id")
                ->where("t.id={$apply[$i]["id"]}")->field("p.pro_name")->select();
            }else{
                $apply[$i]["pro"]=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("pro_products p","td.product=p.id")->group("p.id") 
                ->where("t.id={$apply[$i]["id"]}")->field("p.pro_name")->select();
            }
            //print_r($apply[$i]["pro"]);
            $apply[$i]["yjsjtrfy"]=round(db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("yjsjtrfy"),2);
            $fb=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("fb_shijitourufeiyong");
            if($fb){
                $apply[$i]["sjtrfy"]=round($fb,2);
            }else{
                $apply[$i]["sjtrfy"]=0;
            }
            $apply[$i]["syl"]=round($apply[$i]["sjtrfy"]/$apply[$i]["yjsjtrfy"]*100,2);
            $xl=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("fb_shijixiaoliang");
            if($xl){
                $apply[$i]["xiaoliang"]=round($xl,2);
            }else{
                $apply[$i]["xiaoliang"]='-';
            }
       }
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("所属促销活动执行报表(网点订单类)");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:K1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "所属促销活动执行报表(网点订单类)");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:K2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"活动编号");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"活动主题");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"活动开始时间");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"活动结束时间");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"执行网点数");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"产品名称");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"预计费用额(元)");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"实际投入费用额(元)");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"费用使用率");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"销量汇总(元)");

        $h=3;
        for($i=0;$i<count($apply);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["sn"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,date("Y-m-d",$apply[$i]["start"]),PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,date("Y-m-d",$apply[$i]["end"]),PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["net"],PHPExcel_Cell_DataType::TYPE_STRING);
            $arr=[];
            foreach ($apply[$i]["pro"] as $key => $v) {
                # code...
                $arr[]=$v["pro_name"];
            }
            $str=implode(',',$arr);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$str,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["yjsjtrfy"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["sjtrfy"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["syl"]."%",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["xiaoliang"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
          $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:K'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
           
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //所属活动进度报表
    public function jinzhan($page=1,$list=10){
          //获取所属经销商
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";
        }
        $request = Request::instance();
        $data = $request->post();
        $feiid=db("cg_fifeiyongkemu")->where("title like '%陈列%'")->value("id");
        if($feiid){
            $arr=db("cg_feiyongkemu3")->where("fifeiyongkemu<>$feiid")->field("id")->select();
            $tmparr=[];
            for($i=0;$i<count($arr);$i++){
                $tmparr[]=$arr[$i]["id"];
            }
            if($tmparr){
                $tmparr=implode(',',$tmparr);
                $where.="and td.feetype in ($tmparr) ";
            }else{
                //无费用科目
                $where.="and 1<>1 ";
            }
        }
        
      
        $having="count(t.id)>0 ";
        $ishave=0;
        if($data){
            if(trim($data["sn"])){
                $no=trim($data["sn"]);
                $where.="and t.sn='{$no}' ";
            }
            if(trim($data["title"])){
                $title=trim($data["title"]);
                $where.="and t.title='{$title}' ";
            }
            if($data["start"]){
                $ishave=1;
                $start=strtotime($data["start"]);
                $having.="and max(end)>=$start ";
            }
            if($data["end"]){
                $ishave=1;
                $end=strtotime($data["end"]);
                $having.="and min(start)<=$end ";
            }
        }else{
            $data=["sn"=>"","title"=>"","start"=>"","end"=>""];
        }
        if($ishave){
             $sum=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->having($having)
            ->count();
        }else{
            $sum=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")
            ->count();
        }
        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        if($ishave){
             $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->having($having)->order("t.id")->limit($start,$list)
            ->field("t.sn,t.id,t.type_id,t.title")
            ->select();
        }else{
            $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->order("t.id")->limit($start,$list)
            ->field("t.sn,t.id,t.type_id,t.title")
            ->select();
        }
       
       for($i=0;$i<count($apply);$i++){
            $apply[$i]["start"]=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->min("start");
            $apply[$i]["end"]=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->max("end");
            $net=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]} and net_id is not null")->group("net_id")->count();
            if($net){
                $apply[$i]["net"]=$net;
            }else{
                $apply[$i]["net"]=0;
            }
            if($apply[$i]["type_id"]==1){
                $apply[$i]["pro"]=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("pro_products p","td.tm_id=p.id")->group("p.id")
                ->where("t.id={$apply[$i]["id"]}")->field("p.pro_name")->select();
            }else{
                $apply[$i]["pro"]=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("pro_products p","td.product=p.id")->group("p.id") 
                ->where("t.id={$apply[$i]["id"]}")->field("p.pro_name")->select();
            }
            //print_r($apply[$i]["pro"]);
            $apply[$i]["yjsjtrfy"]=round(db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("yjsjtrfy"),2);
            $fb=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("fb_shijitourufeiyong");
            if($fb){
                $apply[$i]["sjtrfy"]=round($fb,2);
            }else{
                $apply[$i]["sjtrfy"]=0;
            }
            $apply[$i]["syl"]=round($apply[$i]["sjtrfy"]/$apply[$i]["yjsjtrfy"]*100,2);
            $xl=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("fb_shijixiaoliang");
            if($xl){
                $apply[$i]["xiaoliang"]=round($xl,2);
            }else{
                $apply[$i]["xiaoliang"]='-';
            }
       }
        $this->assign("apply",$apply);
        $this->assign("data",$data);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        return $this->fetch();
    }
    public function jinzhan2dc(){
         $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";
        }
        $feiid=db("cg_fifeiyongkemu")->where("title like '%陈列%'")->value("id");
        if($feiid){
            $arr=db("cg_feiyongkemu3")->where("fifeiyongkemu=$feiid")->field("id")->select();
            $tmparr=[];
            for($i=0;$i<count($arr);$i++){
                $tmparr[]=$arr[$i]["id"];
            }
            if($tmparr){
                $tmparr=implode(',',$tmparr);
                $where.="and td.feetype in ($tmparr) ";
            }else{
                //无费用科目
                $where.="and 1<>1 ";
            }
        }else{
             //无费用科目
            $where.="and 1<>1 ";
        }
        
      
        $having="count(t.id)>0 ";
        $ishave=0;
         if($ishave){
             $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->having($having)->order("t.id")
            ->field("t.sn,t.id,t.type_id,t.title")
            ->select();
        }else{
            $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->order("t.id")
            ->field("t.sn,t.id,t.type_id,t.title")
            ->select();
        }
       
       for($i=0;$i<count($apply);$i++){
            $apply[$i]["start"]=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->min("start");
            $apply[$i]["end"]=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->max("end");
            $net=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]} and net_id is not null")->group("net_id")->count();
            if($net){
                $apply[$i]["net"]=$net;
            }else{
                $apply[$i]["net"]=0;
            }
            if($apply[$i]["type_id"]==1){
                $apply[$i]["pro"]=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("pro_products p","td.tm_id=p.id")->group("p.id")
                ->where("t.id={$apply[$i]["id"]}")->field("p.pro_name")->select();
            }else{
                $apply[$i]["pro"]=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("pro_products p","td.product=p.id")->group("p.id") 
                ->where("t.id={$apply[$i]["id"]}")->field("p.pro_name")->select();
            }
            //print_r($apply[$i]["pro"]);
            $apply[$i]["yjsjtrfy"]=round(db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("yjsjtrfy"),2);
            $fb=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("fb_shijitourufeiyong");
            if($fb){
                $apply[$i]["sjtrfy"]=round($fb,2);
            }else{
                $apply[$i]["sjtrfy"]=0;
            }
            $apply[$i]["syl"]=round($apply[$i]["sjtrfy"]/$apply[$i]["yjsjtrfy"]*100,2);
            $xl=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("fb_shijixiaoliang");
            if($xl){
                $apply[$i]["xiaoliang"]=round($xl,2);
            }else{
                $apply[$i]["xiaoliang"]='-';
            }
       }
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("所属促销活动执行报表(网点陈列类)");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:K1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "所属促销活动执行报表(网点陈列类)");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:K2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"活动编号");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"活动主题");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"活动开始时间");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"活动结束时间");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"执行网点数");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"产品名称");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"预计费用额(元)");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"实际投入费用额(元)");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"费用使用率");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"销量汇总(元)");

        $h=3;
        for($i=0;$i<count($apply);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["sn"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,date("Y-m-d",$apply[$i]["start"]),PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,date("Y-m-d",$apply[$i]["end"]),PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["net"],PHPExcel_Cell_DataType::TYPE_STRING);
            $arr=[];
            foreach ($apply[$i]["pro"] as $key => $v) {
                # code...
                $arr[]=$v["pro_name"];
            }
            $str=implode(',',$arr);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$str,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["yjsjtrfy"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["sjtrfy"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["syl"]."%",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["xiaoliang"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
          $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:R'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
           
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //所属促销活动执行报表（网点陈列类）
    public function jinzhan2($page=1,$list=10){
          //获取所属经销商
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";
        }
        $request = Request::instance();
        $data = $request->post();
        $feiid=db("cg_fifeiyongkemu")->where("title like '%陈列%'")->value("id");
        if($feiid){
            $arr=db("cg_feiyongkemu3")->where("fifeiyongkemu=$feiid")->field("id")->select();
            $tmparr=[];
            for($i=0;$i<count($arr);$i++){
                $tmparr[]=$arr[$i]["id"];
            }
            if($tmparr){
                $tmparr=implode(',',$tmparr);
                $where.="and td.feetype in ($tmparr) ";
            }else{
                //无费用科目
                $where.="and 1<>1 ";
            }
        }else{
             //无费用科目
            $where.="and 1<>1 ";
        }
        
      
        $having="count(t.id)>0 ";
        $ishave=0;
        if($data){
            if(trim($data["sn"])){
                $no=trim($data["sn"]);
                $where.="and t.sn='{$no}' ";
            }
            if(trim($data["title"])){
                $title=trim($data["title"]);
                $where.="and t.title='{$title}' ";
            }
            if($data["start"]){
                $ishave=1;
                $start=strtotime($data["start"]);
                $having.="and max(end)>=$start ";
            }
            if($data["end"]){
                $ishave=1;
                $end=strtotime($data["end"]);
                $having.="and min(start)<=$end ";
            }
        }else{
            $data=["sn"=>"","title"=>"","start"=>"","end"=>""];
        }
        if($ishave){
             $sum=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->having($having)
            ->count();
        }else{
            $sum=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")
            ->count();
        }
        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        if($ishave){
             $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->having($having)->order("t.id")->limit($start,$list)
            ->field("t.sn,t.id,t.type_id,t.title")
            ->select();
        }else{
            $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->order("t.id")->limit($start,$list)
            ->field("t.sn,t.id,t.type_id,t.title")
            ->select();
        }
       
       for($i=0;$i<count($apply);$i++){
            $apply[$i]["start"]=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->min("start");
            $apply[$i]["end"]=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->max("end");
            $net=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]} and net_id is not null")->group("net_id")->count();
            if($net){
                $apply[$i]["net"]=$net;
            }else{
                $apply[$i]["net"]=0;
            }
            if($apply[$i]["type_id"]==1){
                $apply[$i]["pro"]=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("pro_products p","td.tm_id=p.id")->group("p.id")
                ->where("t.id={$apply[$i]["id"]}")->field("p.pro_name")->select();
            }else{
                $apply[$i]["pro"]=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("pro_products p","td.product=p.id")->group("p.id") 
                ->where("t.id={$apply[$i]["id"]}")->field("p.pro_name")->select();
            }
            //print_r($apply[$i]["pro"]);
            $apply[$i]["yjsjtrfy"]=round(db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("yjsjtrfy"),2);
            $fb=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("fb_shijitourufeiyong");
            if($fb){
                $apply[$i]["sjtrfy"]=round($fb,2);
            }else{
                $apply[$i]["sjtrfy"]=0;
            }
            $apply[$i]["syl"]=round($apply[$i]["sjtrfy"]/$apply[$i]["yjsjtrfy"]*100,2);
            $xl=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("fb_shijixiaoliang");
            if($xl){
                $apply[$i]["xiaoliang"]=round($xl,2);
            }else{
                $apply[$i]["xiaoliang"]='-';
            }
       }
        $this->assign("apply",$apply);
        $this->assign("data",$data);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        return $this->fetch();
    }
    //我的达成
    public function mysuccess(){
        return $this->fetch();
    }
    //下属达成
    public function xiashusuccess(){
        return $this->fetch();
    }
    //所属发运统计排名导出
    public function fypmdc($cid){
        $nian=Db::table("cg_cainian")->where(["id"=>$cid])->find();
        //获取所属经销商
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="t.sdAgency_id in ({$str}) ";
            $where2="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";
            $where2="1<>1 ";
        }
        //查询当年所有二级考核期
        $kaohe2=db("cg_kaoheqi2 k2")->join("cg_kaoheqi1 k1","k2.pid=k1.id")->where(["k1.cid"=>$cid])->order("k2.starttime")->field("k2.id,k2.starttime,k2.endtime,k2.title")->select();
         $start=date("Y-m-d",$nian["starttime"]);$end=date("Y-m-d",$nian["endtime"]);
        $where.="and t.fyDate>='{$start}' and t.fyDate<='{$end}' ";
        $apply1=db("bx_fy t")->join("cg_jxs j","t.sdAgency_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
             ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")->where($where."and t.fyDate>='{$start}' and t.fyDate<='{$end}'")->group("t.sdAgency_id")->order("j.id")->field("j.title,j.id,sum(fyZheMoney) as zong,z.title as fenqu,p.title as sheng,c.title as shi")->select();
         $apply2=[];
        $applys=[];$jxs=[];
        for($i=0;$i<count($apply1);$i++){
            $jxs[]=$apply1[$i]["id"];
            for($j=0;$j<count($apply2);$j++){
                if($apply1[$i]["id"]==$apply2[$j]["id"]){
                    $apply1[$i]["zong"]=$apply1[$i]["zong"]+$apply2[$j]["zong"];
                }
            }
            $applys[]=$apply1[$i];
        }
        for($i=0;$i<count($apply2);$i++){
            if(!in_array($apply2[$i]["id"], $jxs)){
                $applys[]=$apply2[$i];
            }
        }
        for($i=0;$i<count($applys)-1;$i++){
                for($j=$i;$j<count($applys);$j++){
                    if($applys[$i]["zong"]<=$applys[$j]["zong"]){
                        $tmpapply=$applys[$j];
                        $applys[$j]=$applys[$i];
                        $applys[$i]=$tmpapply;
                    }
                }
            }
            $apply=$applys;
         for($i=0;$i<count($apply);$i++){
            if($apply[$i]["zong"]){
                $apply[$i]["zong"]=round($apply[$i]["zong"],2);
            }else{
                $apply[$i]["zong"]=0;
            }
            $jine=[];
            for($j=0;$j<count($kaohe2);$j++){
                $tmpstart=date("Y-m-d",$kaohe2[$j]["starttime"]);
                $tmpend=date("Y-m-d",$kaohe2[$j]["endtime"]);
                $tmpfei=db("bx_fy")->where("sdAgency_id={$apply[$i]["id"]} and fyDate>='{$tmpstart}' and fyDate<='{$tmpend}'")->sum("fyZheMoney");
                $tmpfei2=db("bx_fy2")->where("jxs_id={$apply[$i]["id"]} and fy2Date>='{$tmpstart}' and fy2Date<='{$tmpend}'")->sum("money");
                $tmpjine=0;
                if($tmpfei){
                    $tmpjine+=round($tmpfei,2);
                }
                if($tmpfei2){
                    $tmpjine+=round($tmpfei2,2);
                }
                $jine[]=$tmpjine;
            }
            $apply[$i]["jine"]=$jine;
        }
         $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("所属单品发运汇总表");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:R1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "所属单品发运汇总表");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:R2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:R3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:R3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:R3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:R3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"经销商名");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"分区");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"省份");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"城市");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"1月");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"2月");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"3月");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"4月");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"5月");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"6月");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"7月");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"8月");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"9月");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"10月");
        $objPHPExcel->getActiveSheet()->setCellValue('P3',"11月");
        $objPHPExcel->getActiveSheet()->setCellValue('Q3',"12月");
        $objPHPExcel->getActiveSheet()->setCellValue('R3',"小计");
        $h=3;
        for($i=0;$i<count($apply);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["fenqu"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$apply[$i]["sheng"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$apply[$i]["shi"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["jine"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$apply[$i]["jine"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["jine"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["jine"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["jine"][4],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["jine"][5],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$apply[$i]["jine"][6],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$apply[$i]["jine"][7],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$apply[$i]["jine"][8],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$apply[$i]["jine"][9],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$apply[$i]["jine"][10],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('Q'.$h,$apply[$i]["jine"][11],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('R'.$h,$apply[$i]["zong"],PHPExcel_Cell_DataType::TYPE_STRING);
        }

                $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:R'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //所属发运统计查询
    public function fypm($cid='',$type=5){
        $this->assign("type",$type);
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now &&endtime>=$now")->value("id");
        }
        $nian=Db::table("cg_cainian")->where(["id"=>$cid])->find();
        $this->assign("nian",$nian);
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
         //获取所属经销商
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="t.sdAgency_id in ({$str}) ";
            $where2="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";
            $where2="1<>1 ";
        }
        //查询当年所有二级考核期
        $kaohe2=db("cg_kaoheqi2 k2")->join("cg_kaoheqi1 k1","k2.pid=k1.id")->where(["k1.cid"=>$cid])->order("k2.starttime")->field("k2.id,k2.starttime,k2.endtime,k2.title")->select();
        $this->assign("kaohe2",$kaohe2);
        $this->assign("kaohe2c",count($kaohe2));
        //查询财年开始和结束日期
        $start=date("Y-m-d",$nian["starttime"]);$end=date("Y-m-d",$nian["endtime"]);
        $where.="and t.fyDate>='{$start}' and t.fyDate<='{$end}' ";
        $apply1=db("bx_fy t")->join("cg_jxs j","t.sdAgency_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
             ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")->where($where."and t.fyDate>='{$start}' and t.fyDate<='{$end}'")->group("t.sdAgency_id")->order("j.id")->field("j.title,j.id,sum(fyZheMoney) as zong,z.title as fenqu,p.title as sheng,c.title as shi")->select();
        /*$apply2=db("bx_fy2 t")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
             ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")->where($where2."and t.fy2Date>='{$start}' and t.fy2Date<='{$end}'")->group("t.jxs_id")->order("j.id")->field("j.title,j.id,sum(money) as zong,z.title as fenqu,p.title as sheng,c.title as shi")->select();
    */
   $apply2=[];
        $applys=[];$jxs=[];
        for($i=0;$i<count($apply1);$i++){
            $jxs[]=$apply1[$i]["id"];
            for($j=0;$j<count($apply2);$j++){
                if($apply1[$i]["id"]==$apply2[$j]["id"]){
                    $apply1[$i]["zong"]=$apply1[$i]["zong"]+$apply2[$j]["zong"];
                }
            }
            $applys[]=$apply1[$i];
        }
        for($i=0;$i<count($apply2);$i++){
            if(!in_array($apply2[$i]["id"], $jxs)){
                $applys[]=$apply2[$i];
            }
        }
        //排序
        if($type==1){
            $apply=[];
            for($i=0;$i<count($applys)-1;$i++){
                for($j=$i;$j<count($applys);$j++){
                    if($applys[$i]["zong"]<=$applys[$j]["zong"]){
                        $tmpapply=$applys[$j];
                        $applys[$j]=$applys[$i];
                        $applys[$i]=$tmpapply;
                    }
                }
            }
            for($i=0;$i<count($applys)&&$i<5;$i++){
                $apply[]=$applys[$i];
            }
        }else if($type==2){
             $apply=[];
            for($i=0;$i<count($applys)-1;$i++){
                for($j=$i;$j<count($applys);$j++){
                    if($applys[$i]["zong"]<=$applys[$j]["zong"]){
                        $tmpapply=$applys[$j];
                        $applys[$j]=$applys[$i];
                        $applys[$i]=$tmpapply;
                    }
                }
            }
            for($i=0;$i<count($applys)&&$i<20;$i++){
                $apply[]=$applys[$i];
            }
        }else if($type==3){
             $apply=[];
            for($i=0;$i<count($applys)-1;$i++){
                for($j=$i;$j<count($applys);$j++){
                    if($applys[$i]["zong"]<=$applys[$j]["zong"]){
                        $tmpapply=$applys[$j];
                        $applys[$j]=$applys[$i];
                        $applys[$i]=$tmpapply;
                    }
                }
            }
            for($i=0;$i<count($applys)&&$i<50;$i++){
                $apply[]=$applys[$i];
            }
        }else if($type==4){
             $apply=[];
            for($i=0;$i<count($applys)-1;$i++){
                for($j=$i;$j<count($applys);$j++){
                    if($applys[$i]["zong"]>=$applys[$j]["zong"]){
                        $tmpapply=$applys[$j];
                        $applys[$j]=$applys[$i];
                        $applys[$i]=$tmpapply;
                    }
                }
            }
            for($i=0;$i<count($applys)&&$i<50;$i++){
                $apply[]=$applys[$i];
            }
        }else if($type==5){
            for($i=0;$i<count($applys)-1;$i++){
                for($j=$i;$j<count($applys);$j++){
                    if($applys[$i]["zong"]<=$applys[$j]["zong"]){
                        $tmpapply=$applys[$j];
                        $applys[$j]=$applys[$i];
                        $applys[$i]=$tmpapply;
                    }
                }
            }
            $apply=$applys;
        }else{
            $apply=[];
        }
        
        for($i=0;$i<count($apply);$i++){
            if($apply[$i]["zong"]){
                $apply[$i]["zong"]=round($apply[$i]["zong"],2);
            }else{
                $apply[$i]["zong"]=0;
            }
            $jine=[];
            for($j=0;$j<count($kaohe2);$j++){
                $tmpstart=date("Y-m-d",$kaohe2[$j]["starttime"]);
                $tmpend=date("Y-m-d",$kaohe2[$j]["endtime"]);
                $tmpfei=db("bx_fy")->where("sdAgency_id={$apply[$i]["id"]} and fyDate>='{$tmpstart}' and fyDate<='{$tmpend}'")->sum("fyZheMoney");
                $tmpfei2=db("bx_fy2")->where("jxs_id={$apply[$i]["id"]} and fy2Date>='{$tmpstart}' and fy2Date<='{$tmpend}'")->sum("money");
                $tmpjine=0;
                if($tmpfei){
                    $tmpjine+=round($tmpfei,2);
                }
                if($tmpfei2){
                    $tmpjine+=round($tmpfei2,2);
                }
                $jine[]=$tmpjine;
            }
            $apply[$i]["jine"]=$jine;
        }
        $this->assign("apply",$apply);
        return $this->fetch();
    }
    //所属经销商发运明细查询导出
    public function fycxdc(){
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="h.sdAgency_id in ({$str}) ";
           
        }else{
            $where="1<>1 ";
           
        }
        $apply=db("bx_fy h")->join("cg_jxs j","h.sdAgency_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")->join("cg_net n","h.sdNet_id=n.id","LEFT")
        ->join("pro_products pp","h.product_id=pp.id")->join("pro_sku sk","pp.sku_id=sk.id")
        ->join("pro_series ps","sk.series_id=ps.id")->join("pro_category pc","ps.category_id=pc.id")
        ->join("pro_brands pb","pb.id=pc.brand_id")->where($where)->order("h.fydate,h.id")
        ->field("z.title as fenqu,p.title as sheng,c.title as shi,j.title as jxs,j.sn as jsn,n.title as net,n.sn as netsn,pp.pro_no,pp.pro_name,h.id,h.fyNum,h.fyBasicUnit,h.fyZheMoney,h.fyMaoMoney,h.fyJingMoney,h.fyDate,h.orderNumber,h.fyONumber,h.batchNumber,h.des,h.salesman")->select();
         $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("所属单品发运汇总表");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:U1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "所属单品发运汇总表");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:U2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:U3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:U3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:U3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:U3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"分区");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"省份");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"城市");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"售达方经销商编号");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"售达方经销商名称");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"售达方网点编号");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"售达方网点名称");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"产品编码");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"产品名称");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"数量");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"发运基本单位");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"发运开票金额");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"毛发运金额");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"净发运金额");
        $objPHPExcel->getActiveSheet()->setCellValue('P3',"发运日期");
        $objPHPExcel->getActiveSheet()->setCellValue('Q3',"订单号");
        $objPHPExcel->getActiveSheet()->setCellValue('R3',"发运单号");
        $objPHPExcel->getActiveSheet()->setCellValue('S3',"批号/批次");
        $objPHPExcel->getActiveSheet()->setCellValue('T3',"备注");
        $objPHPExcel->getActiveSheet()->setCellValue('U3',"销售人员");

        $h=3;
        for($i=0;$i<count($apply);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["fenqu"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["sheng"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$apply[$i]["shi"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$apply[$i]["jsn"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["jxs"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$apply[$i]["netsn"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["net"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["pro_no"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["pro_name"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["fyNum"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$apply[$i]["fyBasicUnit"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$apply[$i]["fyZheMoney"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$apply[$i]["fyMaoMoney"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$apply[$i]["fyJingMoney"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$apply[$i]["fyDate"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('Q'.$h,$apply[$i]["orderNumber"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('R'.$h,$apply[$i]["fyONumber"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('S'.$h,$apply[$i]["batchNumber"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('T'.$h,$apply[$i]["des"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('U'.$h,$apply[$i]["salesman"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:U'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //所属经销商发运明细查询
    public function fycx($page=1,$list=10){
        $request = Request::instance();
        $data = $request->post();
          //获取所属经销商
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="h.sdAgency_id in ({$str}) ";
           
        }else{
            $where="1<>1 ";
           
        }
        if($data){
            if($data["fyType"]){
                if($data["fyType"]==1){
                    $where.="and h.fyType=1 ";
                }else{
                    $where.="and h.fyType=0 ";
                }
            }
            if(trim($data["fenqu"])){
                $fenqu=trim($data["fenqu"]);
                $where.="and j.title='$fenqu' ";
            }
            if(trim($data["sheng"])){
                $sheng=trim($data["sheng"]);
                $where.="and p.title='$sheng' ";
            }
            if(trim($data["shi"])){
                $shi=trim($data["shi"]);
                $where.="and c.title='$shi' ";
            }
            if(trim($data["jno"])){
                $jno=trim($data["jno"]);
                $where.="and j.sn='$jno' ";
            }
            if(trim($data["jname"])){
                $jname=trim($data["jname"]);
                $where.="and j.title='$jname' ";
            }
            if(trim($data["nno"])){
                $nno=trim($data["nno"]);
                $where.="and n.sn='$nno' ";
            }
            if(trim($data["nname"])){
                $nname=trim($data["nname"]);
                $where.="and n.title='$nname' ";
            }
            if(trim($data["brand"])){
                $brand=trim($data["brand"]);
                $where.="and pb.brand_name='$brand' ";
            }
            if(trim($data["category"])){
                $category=trim($data["category"]);
                $where.="and pc.category_id='$category' ";
            }
            if(trim($data["series"])){
                $series=trim($data["series"]);
                $where.="and ps.series_id='$series' ";
            }
            if(trim($data["sku"])){
                $sku=trim($data["sku"]);
                $where.="and sk.sku_name='$sku' ";
            }
            if(trim($data["pno"])){
                $pno=trim($data["pno"]);
                $where.="and pp.pro_no='$pno' ";
            }
            if(trim($data["pname"])){
                $pname=trim($data["pname"]);
                $where.="and pp.pro_name='$pname' ";
            }
            if($data["start"]){
                $where.="and h.fyDate>='{$data["start"]}' ";
            }
            if($data["end"]){
                $where.="and h.fyDate<='{$data["end"]}' ";
            }
        }else{
            $data=["fyType"=>0,"fenqu"=>"","sheng"=>"","shi"=>"","jno"=>"","jname"=>"","nno"=>"","nname"=>"","brand"=>"","category"=>"","series"=>"","sku"=>"","pno"=>"","pname"=>"","start"=>"","end"=>""];
        }
        $sum=db("bx_fy h")->join("cg_jxs j","h.sdAgency_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")->join("cg_net n","h.sdNet_id=n.id","LEFT")
        ->join("pro_products pp","h.product_id=pp.id")->join("pro_sku sk","pp.sku_id=sk.id")
        ->join("pro_series ps","sk.series_id=ps.id")->join("pro_category pc","ps.category_id=pc.id")
        ->join("pro_brands pb","pb.id=pc.brand_id")->where($where)->count();
         $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $apply=db("bx_fy h")->join("cg_jxs j","h.sdAgency_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")->join("cg_net n","h.sdNet_id=n.id","LEFT")
        ->join("pro_products pp","h.product_id=pp.id")->join("pro_sku sk","pp.sku_id=sk.id")
        ->join("pro_series ps","sk.series_id=ps.id")->join("pro_category pc","ps.category_id=pc.id")
        ->join("pro_brands pb","pb.id=pc.brand_id")->where($where)->order("h.fydate,h.id")->limit($start,$list)
        ->field("z.title as fenqu,p.title as sheng,c.title as shi,j.title as jxs,j.sn as jsn,n.title as net,n.sn as netsn,pp.pro_no,pp.pro_name,h.id,h.fyNum,h.fyBasicUnit,h.fyZheMoney,h.fyMaoMoney,h.fyJingMoney,h.fyDate,h.orderNumber,h.fyONumber,h.batchNumber,h.des,h.salesman")->select();
        $this->assign("data", $data);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);

        $this->assign("apply",$apply);
        $sumshuliang=db("bx_fy h")->join("cg_jxs j","h.sdAgency_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")->join("cg_net n","h.sdNet_id=n.id","LEFT")
        ->join("pro_products pp","h.product_id=pp.id")->join("pro_sku sk","pp.sku_id=sk.id")
        ->join("pro_series ps","sk.series_id=ps.id")->join("pro_category pc","ps.category_id=pc.id")
        ->join("pro_brands pb","pb.id=pc.brand_id")->where($where)->sum("fyNum");
        if(!$sumshuliang){
            $sumshuliang=0;
        }
        $sumjine=db("bx_fy h")->join("cg_jxs j","h.sdAgency_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")->join("cg_net n","h.sdNet_id=n.id","LEFT")
        ->join("pro_products pp","h.product_id=pp.id")->join("pro_sku sk","pp.sku_id=sk.id")
        ->join("pro_series ps","sk.series_id=ps.id")->join("pro_category pc","ps.category_id=pc.id")
        ->join("pro_brands pb","pb.id=pc.brand_id")->where($where)->sum("fyZheMoney");
        if(!$sumjine){
            $sumjine=0;
        }
        $this->assign("sumshuliang",$sumshuliang);
        $this->assign("sumjine",$sumjine);
        return $this->fetch();
    }
    //所属单品汇总发运导出
    public function danpinhuizongdc(){
          //获取所属经销商
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="h.sdAgency_id in ({$str}) ";
           
        }else{
            $where="1<>1 ";
           
        }
        $apply=db("bx_fy h")->join("cg_jxs j","h.sdAgency_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")->join("cg_net n","h.sdNet_id=n.id","LEFT")
        ->join("pro_products pp","h.product_id=pp.id")->join("pro_sku sk","pp.sku_id=sk.id")
        ->join("pro_series ps","sk.series_id=ps.id")->join("pro_category pc","ps.category_id=pc.id")
        ->join("pro_brands pb","pb.id=pc.brand_id")->where($where)->group("pp.id")->order("p.id")
        ->field("pc.category_name,sk.sku_name,pp.pro_name,pp.format,pp.unit,sum(h.fyZheMoney) as money,pp.id")->select();
        for($i=0;$i<count($apply);$i++){
            $apply[$i]["dazeng"]=db("bx_fy")->where(["fyType"=>1,"product_id"=>$apply[$i]["id"]])->sum("fyNum");
            if(!$apply[$i]["dazeng"]){
                $apply[$i]["dazeng"]=0;
            }
            $apply[$i]["shuliang"]=db("bx_fy")->where(["fyType"=>0,"product_id"=>$apply[$i]["id"]])->sum("fyNum");
            if(!$apply[$i]["shuliang"]){
                $apply[$i]["shuliang"]=0;
            }
        }
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("所属单品发运汇总表");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:H1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "所属单品发运汇总表");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:H2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);

        $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"产品品类");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"sku");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"产品名称");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"规格");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"单位");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"发运数量");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"搭赠数量");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"发运金额(元)");

        $h=3;
        for($i=0;$i<count($apply);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$apply[$i]["category_name"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["sku_name"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["pro_name"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$apply[$i]["format"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$apply[$i]["unit"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["shuliang"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$apply[$i]["dazeng"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["money"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
         $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:H'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //所属单品汇总发运
    public function danpinhuizong($page=1,$list=10){
        $request = Request::instance();
        $data = $request->post();
          //获取所属经销商
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="h.sdAgency_id in ({$str}) ";
           
        }else{
            $where="1<>1 ";
           
        }
        $request = Request::instance();
        $data = $request->post();
          //获取所属经销商
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="h.sdAgency_id in ({$str}) ";
           
        }else{
            $where="1<>1 ";
           
        }
        if($data){
           
            if(trim($data["fenqu"])){
                $fenqu=trim($data["fenqu"]);
                $where.="and j.title='$fenqu' ";
            }
            if(trim($data["sheng"])){
                $sheng=trim($data["sheng"]);
                $where.="and p.title='$sheng' ";
            }
            if(trim($data["shi"])){
                $shi=trim($data["shi"]);
                $where.="and c.title='$shi' ";
            }
            if(trim($data["jno"])){
                $jno=trim($data["jno"]);
                $where.="and j.sn='$jno' ";
            }
            if(trim($data["jname"])){
                $jname=trim($data["jname"]);
                $where.="and j.title='$jname' ";
            }
            if(trim($data["nno"])){
                $nno=trim($data["nno"]);
                $where.="and n.sn='$nno' ";
            }
            if(trim($data["nname"])){
                $nname=trim($data["nname"]);
                $where.="and n.title='$nname' ";
            }
            if(trim($data["brand"])){
                $brand=trim($data["brand"]);
                $where.="and pb.brand_name='$brand' ";
            }
            if(trim($data["category"])){
                $category=trim($data["category"]);
                $where.="and pc.category_id='$category' ";
            }
            if(trim($data["series"])){
                $series=trim($data["series"]);
                $where.="and ps.series_id='$series' ";
            }
            if(trim($data["sku"])){
                $sku=trim($data["sku"]);
                $where.="and sk.sku_name='$sku' ";
            }
            if(trim($data["pno"])){
                $pno=trim($data["pno"]);
                $where.="and pp.pro_no='$pno' ";
            }
            if(trim($data["pname"])){
                $pname=trim($data["pname"]);
                $where.="and pp.pro_name='$pname' ";
            }
            if($data["start"]){
                $where.="and h.fyDate>='{$data["start"]}' ";
            }
            if($data["end"]){
                $where.="and h.fyDate<='{$data["end"]}' ";
            }
        }else{
            $data=["fenqu"=>"","sheng"=>"","shi"=>"","jno"=>"","jname"=>"","nno"=>"","nname"=>"","brand"=>"","category"=>"","series"=>"","sku"=>"","pno"=>"","pname"=>"","start"=>"","end"=>""];
        }
        $sum=db("bx_fy h")->join("cg_jxs j","h.sdAgency_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")->join("cg_net n","h.sdNet_id=n.id","LEFT")
        ->join("pro_products pp","h.product_id=pp.id")->join("pro_sku sk","pp.sku_id=sk.id")
        ->join("pro_series ps","sk.series_id=ps.id")->join("pro_category pc","ps.category_id=pc.id")
        ->join("pro_brands pb","pb.id=pc.brand_id")->where($where)->group("pp.id")->count();
         $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $apply=db("bx_fy h")->join("cg_jxs j","h.sdAgency_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")->join("cg_net n","h.sdNet_id=n.id","LEFT")
        ->join("pro_products pp","h.product_id=pp.id")->join("pro_sku sk","pp.sku_id=sk.id")
        ->join("pro_series ps","sk.series_id=ps.id")->join("pro_category pc","ps.category_id=pc.id")
        ->join("pro_brands pb","pb.id=pc.brand_id")->where($where)->group("pp.id")->order("p.id")->limit($start,$list)
        ->field("pc.category_name,sk.sku_name,pp.pro_name,pp.format,pp.unit,sum(h.fyZheMoney) as money,pp.id")->select();
        for($i=0;$i<count($apply);$i++){
            $apply[$i]["dazeng"]=db("bx_fy")->where(["fyType"=>1,"product_id"=>$apply[$i]["id"]])->sum("fyNum");
            if(!$apply[$i]["dazeng"]){
                $apply[$i]["dazeng"]=0;
            }
            $apply[$i]["shuliang"]=db("bx_fy")->where(["fyType"=>0,"product_id"=>$apply[$i]["id"]])->sum("fyNum");
            if(!$apply[$i]["shuliang"]){
                $apply[$i]["shuliang"]=0;
            }
        }
        $this->assign("data", $data);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);

        $this->assign("apply",$apply);
       

        return $this->fetch();
    }
    //库存分仓查询
    public function kucun1($page=1,$list=10){
        $request = Request::instance();
        $data = $request->post();
        $canku=db("cg_ck")->select();
        $wt=db("pro_material_type")->select();
        $this->assign("wt",$wt);
        $this->assign("canku",$canku);
        $where="1=1 ";
        if($data){
            if(trim($data["wlbh"])){
                $wlbh=trim($data["wlbh"]);
                $where.="and kc.wlbh='$wlbh' ";
            }
            if(trim($data["wlmc"])){
                $wlmc=trim($data["wlmc"]);
                $where.="and kc.wlmc='$wlmc' ";
            }
            if($data["wt"]){
                $where.="and kc.wlxl={$data["wt"]} ";
            }
            if(trim($data["cppl"])){
                $cppl=trim($data["cppl"]);
                $where.="and kc.cppl='$cppl' ";
            }
            if($data["ck"]){
                $where.="and kc.ck={$data["ck"]} ";
            }
        }else{
            $data=["wlbh"=>"","wlmc"=>"","wt"=>0,"cppl"=>"","ck"=>0];
        }
        $sum=Db("yd_kccx kc")->join("cg_ck ck","kc.ck=ck.id")->where($where)->count();
         $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $apply=Db("yd_kccx kc")->join("cg_ck ck","kc.ck=ck.id")->where($where)->order("kc.id")->limit($start,$list)
        ->field("ck.ckqc,kc.wlmc,kc.cppl,kc.wlbh,kc.wlmc,kc.gg,kc.kcdw,kc.kcsl,kc.wllb")->select();
        $this->assign("apply",$apply);
          $this->assign("data", $data);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        return $this->fetch();
    }
    //库存不分仓
    public function kucun2($page=1,$list=10){
         $request = Request::instance();
        $data = $request->post();
       
        $wt=db("pro_material_type")->select();
        $this->assign("wt",$wt);
       
        $where="1=1 ";
        if($data){
            if(trim($data["wlbh"])){
                $wlbh=trim($data["wlbh"]);
                $where.="and kc.wlbh='$wlbh' ";
            }
            if(trim($data["wlmc"])){
                $wlmc=trim($data["wlmc"]);
                $where.="and kc.wlmc='$wlmc' ";
            }
            if($data["wt"]){
                $where.="and kc.wlxl={$data["wt"]} ";
            }
            if(trim($data["cppl"])){
                $cppl=trim($data["cppl"]);
                $where.="and kc.cppl='$cppl' ";
            }
           
        }else{
            $data=["wlbh"=>"","wlmc"=>"","wt"=>0,"cppl"=>""];
        }
         $sum=Db("yd_kccx kc")->join("cg_ck ck","kc.ck=ck.id")->group("kc.wlbh")->where($where)->count();
         $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $apply=Db("yd_kccx kc")->join("cg_ck ck","kc.ck=ck.id")->group("kc.wlbh")->where($where)->order("kc.id")->limit($start,$list)
        ->field("ck.ckqc,kc.wlmc,kc.cppl,kc.wlbh,kc.wlmc,kc.gg,kc.kcdw,sum(kc.kcsl) as kcsl,kc.wllb")->select();
        $this->assign("apply",$apply);
          $this->assign("data", $data);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        return $this->fetch();
    }
    //所属经销商目标
    public function jxsmubiao($cid=0,$pid=0,$jids=0){
        //echo $jids;
        $this->assign("pid",$pid);
        $tid=5;
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
        $this->assign("nian",$nian);
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $brand=db("pro_brands")->select();
        $this->assign("brand",$brand);
        
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="td.jingxiao in ({$str}) and t.type_id=$tid and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.cainian=$cid ";
           
        }else{
            $where="1<>1 ";
           
        }
        if($pid){
            $where.="and td.pinpai={$pid} ";
        }
        $jxss=[];
        if($jids){
            $where.="and td.jingxiao in ($jids) ";
            $jxss=db("cg_jxs")->where("id in ($jids)")->select();
        }

        $this->assign("jxss",$jxss);
        $kaohe2=db("cg_kaoheqi2 k2")->join("cg_kaoheqi1 k1","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k1.starttime as k1start,k1.endtime as k1end,k1.id as k1id,k2.starttime as k2start,k2.endtime as k2end,k2.id as k2id,k2.title as yue")->select();
        $this->assign("kaohe2",$kaohe2);
        $this->assign("kaohe2c",count($kaohe2));
        for($i=0;$i<count($kaohe2);$i++){
            $kaohe2[$i]["k1ts"]=($kaohe2[$i]["k1end"]-$kaohe2[$i]["k1start"])/(3600*24)+1;
            $kaohe2[$i]["k2ts"]=($kaohe2[$i]["k2end"]-$kaohe2[$i]["k2start"])/(3600*24)+1;
            
        }
        $jarr=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")->join("cg_jxs j","td.jingxiao=j.id")
        ->where($where)->group("td.jingxiao")
        ->field("td.jingxiao,j.title as jx,sum(td.feiyong) as zfy")->select();
        for($i=0;$i<count($jarr);$i++){
            $jarr[$i]["pro"]=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")->join("cg_jxs j","td.jingxiao=j.id")
            ->join("pro_brands p","td.pinpai=p.id")
            ->where($where."and td.jingxiao={$jarr[$i]["jingxiao"]}")->group("td.pinpai")->field("p.brand_name,p.id")->select();
            $jarr[$i]["pc"]=count($jarr[$i]["pro"]);
            $jarr[$i]["jine"]=[0,0,0,0,0,0,0,0,0,0,0,0];

            for($j=0;$j<count($jarr[$i]["pro"]);$j++){
                $jarr[$i]["pro"][$j]["jin"]=[];$jarr[$i]["pro"][$j]["zje"]=0;
                for($k=0;$k<count($kaohe2);$k++){
                    $tmpj=0;
                    $erji=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")->where($where."and td.jingxiao={$jarr[$i]["jingxiao"]} and td.pinpai={$jarr[$i]["pro"][$j]["id"]} and td.erjikaoheqi={$kaohe2[$k]["k2id"]}")->sum("feiyong");
                    $yiji=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")->where($where."and td.jingxiao={$jarr[$i]["jingxiao"]} and td.pinpai={$jarr[$i]["pro"][$j]["id"]} and td.yijikaoheqi={$kaohe2[$k]["k1id"]} and td.erjikaoheqi is null")->sum("feiyong");
                    if($erji)
                        $tmpj+=$erji;
                    if($yiji){
                        $tmpj=$tmpj+$yiji/$kaohe2[$k]["k1ts"]*$kaohe2[$k]["k2ts"];
                    }
                    $jarr[$i]["pro"][$j]["jin"][]=$tmpj;
                    $jarr[$i]["pro"][$j]["zje"]+=$tmpj;
                    $jarr[$i]["jine"][$k]+=$tmpj;
                }
            }
            $jarr[$i]["zjine"]=0;
            foreach ($jarr[$i]["jine"] as $key => $value) {
                # code...
                $jarr[$i]["zjine"]+=$value;
            }
        }
        $this->assign("jarr",$jarr);
        return $this->fetch();
    }
    //网点销售统计
    public function nettongji($page=1,$list=10){
        $request = Request::instance();
        $data = $request->post();
        $netlist=$this->my_own_net();
        $swhere="t.status=7 and (t.status2 is null or t.status2=0) ";
        if($netlist){
            $netstr=implode(',',$netlist);
           $where="id in ($netstr) ";
        }else{
           $where="1<>1 ";
        }
        if($data){
            if(trim($data["netsn"])){
                $sn=trim($data["netsn"]);
                $where.="and sn='$sn' ";
            }
            if(trim($data["net"])){
                $net=trim($data["net"]);
                $where.="and title='$net' ";
            }
            if($data["start"]){
                $start=strtotime($data["start"]);
                $swhere.="and td.start>=$start ";
            }
            if($data["end"]){
                $end=strtotime($data["end"]);
                $swhere.="and td.start<=$end ";
            }
        }else{
            $data=["netsn"=>"","net"=>"","start"=>"","end"=>""];
        }
        $sum=Db("cg_net")->where($where)->count();
         $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $apply=Db("cg_net")->where($where)->order("id")->limit($start,$list)->select();
        for($i=0;$i<count($apply);$i++){
            $xl=Db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where($swhere."and td.net_id={$apply[$i]["id"]}")->field("td.totalExpectedSales,td.fb_shijixiaoliang")->select();
            $apply[$i]["xl"]=0;
            foreach ($xl as $k => $v) {
                # code...
                if($v["fb_shijixiaoliang"]){
                     $apply[$i]["xl"]+=$v["fb_shijixiaoliang"];
                }else{
                    $apply[$i]["xl"]+=$v["totalExpectedSales"];
                }
            }
             $apply[$i]["xl"]=round( $apply[$i]["xl"],2);
        }
         $this->assign("apply",$apply);
         $this->assign("data", $data);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        return $this->fetch();
    }
    //所属经销商达成导出
    public function jxsdcout($cid,$jids){
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->select();
        $jids=explode('-',$jids);
        $jids=implode(',',$jids);
        $jxss=db("cg_jxs")->where("id in ($jids)")->select();
        $jarr=[];
        for($i=0;$i<count($jxss);$i++){
            $jarr[$i]["title"]=$jxss[$i]["title"];
            $jarr[$i]["zyjxl"]=0;$jarr[$i]["zyjfy"]=0;$jarr[$i]["zxldc"]=0;$jarr[$i]["zfysq"]=0;$jarr[$i]["zyjfyl"]=0;
            $jarr[$i]["zsjfyl"]=0;$jarr[$i]["zdcl"]='-';
            for($j=0;$j<count($kaohe1);$j++){
                $kaohe1[$j]["endtime"]=$kaohe1[$j]["endtime"]+3600*24;
                //预计销量
                $yjxl=0;
                $tmpxl=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.jingxiao={$jxss[$i]["id"]} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpxl){
                    $yjxl=round($tmpxl,2);
                }
                $jarr[$i]["yjxl"][$j]=$yjxl;
                $jarr[$i]["zyjxl"]+=$yjxl;
                //预计费用预算
                $yjfy=0;
                $tmpfy=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.jingxiao={$jxss[$i]["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpfy){
                    $yjfy=round($tmpfy,2);
                }
                $jarr[$i]["yjfy"][$j]=$yjfy;
                $jarr[$i]["zyjfy"]+=$yjfy;
                //销量达成
                $xldc=0;
                $tmpxdc=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.jxs_id={$jxss[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$kaohe1[$j]["endtime"]}")->field("totalExpectedSales,fb_shijixiaoliang")->select();
                foreach ($tmpxdc as $k => $v) {
                    # code...
                    if($v["fb_shijixiaoliang"]){
                        $xldc+=$v["fb_shijixiaoliang"];
                    }else{
                        $xldc+=$v["totalExpectedSales"];
                    }
                }
                $jarr[$i]["xldc"][$j]=round($xldc,2);
                $jarr[$i]["zxldc"]+=round($xldc,2);
                //费用申请
                $fysq=0;
                $tmpsq=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.jxs_id={$jxss[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$kaohe1[$j]["endtime"]}")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                foreach ($tmpsq as $k => $v) {
                    # code...
                    if($v["fb_shijitourufeiyong"]){
                        $fysq+=$v["fb_shijitourufeiyong"];
                    }else{
                        $fysq+=$v["yjsjtrfy"];
                    }
                }
                $jarr[$i]["fysq"][$j]=round($fysq,2);
                $jarr[$i]["zfysq"]+=round($fysq,2);
                if($jarr[$i]["yjxl"][$j]){
                    $jarr[$i]["yjfyl"][$j]=round($jarr[$i]["yjfy"][$j]/$jarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $jarr[$i]["yjfyl"][$j]=0;
                }

                if($jarr[$i]["xldc"][$j]){
                    $jarr[$i]["sjfyl"][$j]=round($jarr[$i]["fysq"][$j]/$jarr[$i]["xldc"][$j]*100,2);
                }else{
                    $jarr[$i]["sjfyl"][$j]=0;
                }

                if($jarr[$i]["yjxl"][$j]){
                    $jarr[$i]["dcl"][$j]=round($jarr[$i]["xldc"][$j]/$jarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $jarr[$i]["dcl"][$j]='-';
                }
            }
            if($jarr[$i]["zyjxl"]){
                $jarr[$i]["zyjfyl"]=round($jarr[$i]["zyjfy"]/$jarr[$i]["zyjxl"]*100,2);
            }
            if($jarr[$i]["zxldc"]){
                $jarr[$i]["zsjfyl"]=round($jarr[$i]["zfysq"]/$jarr[$i]["zxldc"]*100,2);
            }
            if($jarr[$i]["zyjxl"]){
                $jarr[$i]["zdcl"]=round($jarr[$i]["zxldc"]/$jarr[$i]["zyjxl"]*100,2);
            }
        }
         //excel
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();
        $title=$nian."财年所属目标达成报表";
        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
        $objPHPExcel= new PHPExcel();
        $objProps=$objPHPExcel->getProperties();
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($title);
       
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);

       

        
        $objPHPExcel->getActiveSheet()->mergeCells('A1:G1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "$title");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:G2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
        #####地域信息
        $objPHPExcel->getActiveSheet()->mergeCells('A3:G3');
                 //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
         //字体
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(10);

        $str="包含经销商:";
        if($jarr){
            $tmparr=[];
            foreach ($jarr as $k => $v) {
                # code...
                $tmparr[]=$v["title"];
            }
            $str.=implode(',',$tmparr);
        }else{
            $str.="无";
        }
        $objPHPExcel->getActiveSheet()->setCellValue('A3',$str);

         $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A4:G4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A4:G4')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A4:G4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A4:G4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A4',"经销商名称");
        $objPHPExcel->getActiveSheet()->setCellValue('C4',"第一季度");
        $objPHPExcel->getActiveSheet()->setCellValue('D4',"第二季度");
        $objPHPExcel->getActiveSheet()->setCellValue('E4',"第三季度");
        $objPHPExcel->getActiveSheet()->setCellValue('F4',"第四季度");
        $objPHPExcel->getActiveSheet()->setCellValue('G4',"合计");
        $h=4;
        for($i=0;$i<count($jarr);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':A'.($h+6));
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$jarr[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
             //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"预计销售目标(元)",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["yjxl"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["yjxl"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["yjxl"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["yjxl"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zyjxl"],PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"预计费用预算(元)",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["yjfy"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["yjfy"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["yjfy"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["yjfy"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zyjfy"],PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际销售达成(元)",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["xldc"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["xldc"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["xldc"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["xldc"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zxldc"],PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际费用申请(元)",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["fysq"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["fysq"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["fysq"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["fysq"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zfysq"],PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"预计费用率",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["yjfyl"][0].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["yjfyl"][1].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["yjfyl"][2].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["yjfyl"][3].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zyjfyl"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际费用率",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["sjfyl"][0].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["sjfyl"][1].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["sjfyl"][2].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["sjfyl"][3].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zsjfyl"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际达成率",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["dcl"][0])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["dcl"][0].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
           if($jarr[$i]["dcl"][1])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["dcl"][1].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["dcl"][2])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["dcl"][2].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["dcl"][3])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["dcl"][3].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["zdcl"])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zdcl"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            
          
        }
           $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:G'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //所属经销商达成
    public function jxsdc($cid=0,$jids=0){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
        $this->assign("nian",$nian);
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $jxslist=$this->my_own_jxs();
        if($jxslist)
            $jxss=db("cg_jxs")->where("id in ({$jxslist[0]})")->select();
        else
            $jxss=[];
        if($jids){
           
            $jxss=db("cg_jxs")->where("id in ($jids)")->select();
        }

        $this->assign("jxss",$jxss);
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->select();
        $this->assign("kaohe1",$kaohe1);
        $jarr=[];
        for($i=0;$i<count($jxss);$i++){
            $jarr[$i]["title"]=$jxss[$i]["title"];
            $jarr[$i]["zyjxl"]=0;$jarr[$i]["zyjfy"]=0;$jarr[$i]["zxldc"]=0;$jarr[$i]["zfysq"]=0;$jarr[$i]["zyjfyl"]=0;
            $jarr[$i]["zsjfyl"]=0;$jarr[$i]["zdcl"]='-';
            for($j=0;$j<count($kaohe1);$j++){
                $kaohe1[$j]["endtime"]=$kaohe1[$j]["endtime"]+3600*24;
                //预计销量
                $yjxl=0;
                $tmpxl=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.jingxiao={$jxss[$i]["id"]} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpxl){
                    $yjxl=round($tmpxl,2);
                }
                $jarr[$i]["yjxl"][$j]=$yjxl;
                $jarr[$i]["zyjxl"]+=$yjxl;
                //预计费用预算
                $yjfy=0;
                $tmpfy=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.jingxiao={$jxss[$i]["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpfy){
                    $yjfy=round($tmpfy,2);
                }
                $jarr[$i]["yjfy"][$j]=$yjfy;
                $jarr[$i]["zyjfy"]+=$yjfy;
                //销量达成
                $xldc=0;
                $tmpxdc=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.jxs_id={$jxss[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$kaohe1[$j]["endtime"]}")->field("totalExpectedSales,fb_shijixiaoliang")->select();
                foreach ($tmpxdc as $k => $v) {
                    # code...
                    if($v["fb_shijixiaoliang"]){
                        $xldc+=$v["fb_shijixiaoliang"];
                    }else{
                        $xldc+=$v["totalExpectedSales"];
                    }
                }
                $jarr[$i]["xldc"][$j]=round($xldc,2);
                $jarr[$i]["zxldc"]+=round($xldc,2);
                //费用申请
                $fysq=0;
                $tmpsq=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.jxs_id={$jxss[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$kaohe1[$j]["endtime"]}")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                foreach ($tmpsq as $k => $v) {
                    # code...
                    if($v["fb_shijitourufeiyong"]){
                        $fysq+=$v["fb_shijitourufeiyong"];
                    }else{
                        $fysq+=$v["yjsjtrfy"];
                    }
                }
                $jarr[$i]["fysq"][$j]=round($fysq,2);
                $jarr[$i]["zfysq"]+=round($fysq,2);
                if($jarr[$i]["yjxl"][$j]){
                    $jarr[$i]["yjfyl"][$j]=round($jarr[$i]["yjfy"][$j]/$jarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $jarr[$i]["yjfyl"][$j]=0;
                }

                if($jarr[$i]["xldc"][$j]){
                    $jarr[$i]["sjfyl"][$j]=round($jarr[$i]["fysq"][$j]/$jarr[$i]["xldc"][$j]*100,2);
                }else{
                    $jarr[$i]["sjfyl"][$j]=0;
                }

                if($jarr[$i]["yjxl"][$j]){
                    $jarr[$i]["dcl"][$j]=round($jarr[$i]["xldc"][$j]/$jarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $jarr[$i]["dcl"][$j]='-';
                }
            }
            if($jarr[$i]["zyjxl"]){
                $jarr[$i]["zyjfyl"]=round($jarr[$i]["zyjfy"]/$jarr[$i]["zyjxl"]*100,2);
            }
            if($jarr[$i]["zxldc"]){
                $jarr[$i]["zsjfyl"]=round($jarr[$i]["zfysq"]/$jarr[$i]["zxldc"]*100,2);
            }
            if($jarr[$i]["zyjxl"]){
                $jarr[$i]["zdcl"]=round($jarr[$i]["zxldc"]/$jarr[$i]["zyjxl"]*100,2);
            }
        }
        $this->assign("jarr",$jarr);
        return $this->fetch();
    }
    //分区科目
    public function fenqukemu($cid="",$zid=""){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
      
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $zids=$this->my_own_fenqu();
        //print_r($zids);exit;
        $zids=implode(',',$zids);
        if($zid){
            $zids=$zid;
        }
        if($zids){
            $fenqu=db("cg_market_zoning")->where("id in ($zids)")->select();
            $tmp=db("cg_jxs")->where("zoning in ($zids)")->field("id")->select();
            $jxs=[];
            foreach ($tmp as $key => $v) {
                # code...
                $jxs[]=$v["id"];
            }
        }else{
            $fenqu=[];
            $jxs=[];
        }
        $this->assign("fenqu",$fenqu);
        if($jxs){
            $jxs=implode(',',$jxs);
            $kemu=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_feiyongkemu3 k3","td.feetype=k3.id")
            ->join("cg_feiyongkemu2 k2","k3.pid=k2.id")->join("cg_feiyongkemu1 k1","k2.pid=k1.id")->group("td.feetype")
            ->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]}")
            ->field("td.feetype as id,k3.title as kemu3,k2.title as kemu2,k1.title as kemu1")->select();
        }else{
            $kemu=[];
        }
        $zong=0;
        for($i=0;$i<count($kemu);$i++){
            $tmps=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]} and td.feetype={$kemu[$i]["id"]}")->field("pizhunweishui,fb_shijiweishui,hx_byhx")->select();
            $jine=0;
            foreach ($tmps as $k => $v) {
                # code...
                if($v["fb_shijiweishui"]){
                    $jine+=$v["fb_shijiweishui"];
                }else{
                    $jine+=$v["pizhunweishui"];
                }
                if($v["hx_byhx"]){
                    $jine-=$v["hx_byhx"];
                }
            }

            $kemu[$i]["jine"]=round($jine,2);
            $zong+=round($jine,2);
        }
        foreach ($kemu as $k => $v) {
            # code...
            $kemu[$k]["bili"]=round($v["jine"]/$zong*100,2);
        }
        $this->assign("kemu",$kemu);
        $this->assign("zong",$zong);
        return $this->fetch();
    }
    //省份科目
    public function shengkemu($cid="",$zid=""){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
      
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $zids=$this->my_own_sheng();
        //print_r($zids);exit;
        $zids=implode(',',$zids);
        if($zid){
            $zids=$zid;
        }
        if($zids){
            $fenqu=db("cg_market_provice")->where("id in ($zids)")->select();
            $tmp=db("cg_jxs")->where("provice in ($zids)")->field("id")->select();
            $jxs=[];
            foreach ($tmp as $key => $v) {
                # code...
                $jxs[]=$v["id"];
            }
        }else{
            $fenqu=[];
            $jxs=[];
        }
        $this->assign("fenqu",$fenqu);
        if($jxs){
            $jxs=implode(',',$jxs);
            $kemu=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_feiyongkemu3 k3","td.feetype=k3.id")
            ->join("cg_feiyongkemu2 k2","k3.pid=k2.id")->join("cg_feiyongkemu1 k1","k2.pid=k1.id")->group("td.feetype")
            ->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]}")
            ->field("td.feetype as id,k3.title as kemu3,k2.title as kemu2,k1.title as kemu1")->select();
        }else{
            $kemu=[];
        }
        $zong=0;
        for($i=0;$i<count($kemu);$i++){
            $tmps=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]} and td.feetype={$kemu[$i]["id"]}")->field("pizhunweishui,fb_shijiweishui,hx_byhx")->select();
            $jine=0;
            foreach ($tmps as $k => $v) {
                # code...
                if($v["fb_shijiweishui"]){
                    $jine+=$v["fb_shijiweishui"];
                }else{
                    $jine+=$v["pizhunweishui"];
                }
                if($v["hx_byhx"]){
                    $jine-=$v["hx_byhx"];
                }
            }

            $kemu[$i]["jine"]=round($jine,2);
            $zong+=round($jine,2);
        }
        foreach ($kemu as $k => $v) {
            # code...
            $kemu[$k]["bili"]=round($v["jine"]/$zong*100,2);
        }
        $this->assign("kemu",$kemu);
        $this->assign("zong",$zong);
        return $this->fetch();
    }
    //城市科目
    public function shikemu($cid="",$zid=""){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
      
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $zids=$this->my_own_shi();
        //print_r($zids);exit;
        $zids=implode(',',$zids);
        if($zid){
            $zids=$zid;
        }
        if($zids){
            $fenqu=db("cg_market_city")->where("id in ($zids)")->select();
            $tmp=db("cg_jxs")->where("city in ($zids)")->field("id")->select();
            $jxs=[];
            foreach ($tmp as $key => $v) {
                # code...
                $jxs[]=$v["id"];
            }
        }else{
            $fenqu=[];
            $jxs=[];
        }
        $this->assign("fenqu",$fenqu);
        if($jxs){
            $jxs=implode(',',$jxs);
            $kemu=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_feiyongkemu3 k3","td.feetype=k3.id")
            ->join("cg_feiyongkemu2 k2","k3.pid=k2.id")->join("cg_feiyongkemu1 k1","k2.pid=k1.id")->group("td.feetype")
            ->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]}")
            ->field("td.feetype as id,k3.title as kemu3,k2.title as kemu2,k1.title as kemu1")->select();
        }else{
            $kemu=[];
        }
        $zong=0;
        for($i=0;$i<count($kemu);$i++){
            $tmps=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]} and td.feetype={$kemu[$i]["id"]}")->field("pizhunweishui,fb_shijiweishui,hx_byhx")->select();
            $jine=0;
            foreach ($tmps as $k => $v) {
                # code...
                if($v["fb_shijiweishui"]){
                    $jine+=$v["fb_shijiweishui"];
                }else{
                    $jine+=$v["pizhunweishui"];
                }
                if($v["hx_byhx"]){
                    $jine-=$v["hx_byhx"];
                }
            }

            $kemu[$i]["jine"]=round($jine,2);
            $zong+=round($jine,2);
        }
        foreach ($kemu as $k => $v) {
            # code...
            $kemu[$k]["bili"]=round($v["jine"]/$zong*100,2);
        }
        $this->assign("kemu",$kemu);
        $this->assign("zong",$zong);
        return $this->fetch();
    }
        //经销商科目
    public function jxskemu($cid="",$zid=""){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
      
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $zids=$this->my_own_jxs();
        //print_r($zids);exit;
        $zids=implode(',',$zids);
        if($zid){
            $zids=$zid;
        }
        if($zids){
            $fenqu=db("cg_jxs")->where("id in ($zids)")->select();
            $tmp=db("cg_jxs")->where("id in ($zids)")->field("id")->select();
            $jxs=[];
            foreach ($tmp as $key => $v) {
                # code...
                $jxs[]=$v["id"];
            }
        }else{
            $fenqu=[];
            $jxs=[];
        }
        $this->assign("fenqu",$fenqu);
        if($jxs){
            $jxs=implode(',',$jxs);
            $kemu=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_feiyongkemu3 k3","td.feetype=k3.id")
            ->join("cg_feiyongkemu2 k2","k3.pid=k2.id")->join("cg_feiyongkemu1 k1","k2.pid=k1.id")->group("td.feetype")
            ->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]}")
            ->field("td.feetype as id,k3.title as kemu3,k2.title as kemu2,k1.title as kemu1")->select();
        }else{
            $kemu=[];
        }
        $zong=0;
        for($i=0;$i<count($kemu);$i++){
            $tmps=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]} and td.feetype={$kemu[$i]["id"]}")->field("pizhunweishui,fb_shijiweishui,hx_byhx")->select();
            $jine=0;
            foreach ($tmps as $k => $v) {
                # code...
                if($v["fb_shijiweishui"]){
                    $jine+=$v["fb_shijiweishui"];
                }else{
                    $jine+=$v["pizhunweishui"];
                }
                if($v["hx_byhx"]){
                    $jine-=$v["hx_byhx"];
                }
            }

            $kemu[$i]["jine"]=round($jine,2);
            $zong+=round($jine,2);
        }
        foreach ($kemu as $k => $v) {
            # code...
            $kemu[$k]["bili"]=round($v["jine"]/$zong*100,2);
        }
        $this->assign("kemu",$kemu);
        $this->assign("zong",$zong);
        return $this->fetch();
    }
    //导出科目
    public function kemudaochu($tid,$cid,$zid){
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $zid=explode('-',$zid);
        $zid=implode(',',$zid);
        switch ($tid) {
            case 1:
                # 分区
                $type="分区";
                $area=db("cg_market_zoning")->where("id in ($zid)")->field("id,title")->select();
                $tmp=db("cg_jxs")->where("zoning in ($zid)")->field("id")->select();
                $jxs=[];
                foreach ($tmp as $key => $v) {
                    # code...
                    $jxs[]=$v["id"];
                }
                break;
            case 2:
                # 省份
                $type="省份";
                $area=db("cg_market_provice")->where("id in ($zid)")->field("id,title")->select();
                $tmp=db("cg_jxs")->where("provice in ($zid)")->field("id")->select();
                $jxs=[];
                foreach ($tmp as $key => $v) {
                    # code...
                    $jxs[]=$v["id"];
                }
                break;
            case 3:
                # 城市
                $type="城市";
                $area=db("cg_market_city")->where("id in ($zid)")->field("id,title")->select();
                $tmp=db("cg_jxs")->where("city in ($zid)")->field("id")->select();
                $jxs=[];
                foreach ($tmp as $key => $v) {
                    # code...
                    $jxs[]=$v["id"];
                }
                break;
            default:
                # 经销商
                $type="经销商";
                $area=db("cg_jxs")->where("id in ($zid)")->field("id,title")->select();
                $tmp=db("cg_jxs")->where("id in ($zid)")->field("id")->select();
                $jxs=[];
                foreach ($tmp as $key => $v) {
                    # code...
                    $jxs[]=$v["id"];
                }
                break;
        }
        if($jxs){
            $jxs=implode(',',$jxs);
            $kemu=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_feiyongkemu3 k3","td.feetype=k3.id")
            ->join("cg_feiyongkemu2 k2","k3.pid=k2.id")->join("cg_feiyongkemu1 k1","k2.pid=k1.id")->group("td.feetype")
            ->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]}")
            ->field("td.feetype as id,k3.title as kemu3,k2.title as kemu2,k1.title as kemu1")->select();
        }else{
            $kemu=[];
        }
        $zong=0;
        for($i=0;$i<count($kemu);$i++){
            $tmps=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]} and td.feetype={$kemu[$i]["id"]}")->field("pizhunweishui,fb_shijiweishui,hx_byhx")->select();
            $jine=0;
            foreach ($tmps as $k => $v) {
                # code...
                if($v["fb_shijiweishui"]){
                    $jine+=$v["fb_shijiweishui"];
                }else{
                    $jine+=$v["pizhunweishui"];
                }
                if($v["hx_byhx"]){
                    $jine-=$v["hx_byhx"];
                }
            }

            $kemu[$i]["jine"]=round($jine,2);
            $zong+=round($jine,2);
        }
        foreach ($kemu as $k => $v) {
            # code...
            $kemu[$k]["bili"]=round($v["jine"]/$zong*100,2);
        }
        //导出处理
        $eid=Session::get("employee_id");
        $users=Db::table("employee")->where(["id"=>$eid])->find();
        $title="{$cn["title"]}财年{$type}各科目与总费用占比";
        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
         $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
             $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($title);
       
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);

       

        
          $objPHPExcel->getActiveSheet()->mergeCells('A1:E1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "$title");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:E2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
        #####地域信息
        $objPHPExcel->getActiveSheet()->mergeCells('A3:E3');
                 //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
         //字体
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(10);
        $str="包含{$type}:";
        if($area){
            $tmparr=[];
            foreach ($area as $k => $v) {
                # code...
                $tmparr[]=$v["title"];
            }
            $str.=implode(',',$tmparr);
        }else{
            $str.="无";
        }
        $objPHPExcel->getActiveSheet()->setCellValue('A3',$str);


         $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A4:E4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A4:E4')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A4:E4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A4:E4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A4',"一级费用科目");
        $objPHPExcel->getActiveSheet()->setCellValue('B4',"二级费用科目");
        $objPHPExcel->getActiveSheet()->setCellValue('C4',"三级费用科目");
        $objPHPExcel->getActiveSheet()->setCellValue('D4',"占比");
        $objPHPExcel->getActiveSheet()->setCellValue('E4',"投入额");
        $h=4;
        for($i=0;$i<count($kemu);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$kemu[$i]["kemu1"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$kemu[$i]["kemu2"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$kemu[$i]["kemu3"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$kemu[$i]["bili"]."%",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$kemu[$i]["jine"],PHPExcel_Cell_DataType::TYPE_STRING);      
        }
         $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:E'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
             //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
               $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        //var_dump($objWriter);exit;
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //回款
    public function huikuan(){
        $request = Request::instance();
        $data = $request->post();
        $jxslist=$this->my_own_jxs();
        return $this->fetch();
    }
    //经销商账户
    public function jxszh($page=1,$list=10){
        $request = Request::instance();
        $data = $request->post();
        $jxslist=$this->my_own_jxs();
        $jxs=implode(',',$jxslist);
        if($jxs){
            $where="j.id in ($jxs) ";
        }else{
            $where="1<>1 ";
        }
        if($data){
            if(trim($data["no"])){
                $no=trim($data["no"]);
                $where.="and j.sn='{$no}' ";
            }
            if(trim($data["jxs"])){
                $jxs=trim($data["jxs"]);
                $where.="and j.title='{$jxs}' ";
            }
            if(trim($data["fenqu"])){
                $fenqu=trim($data["fenqu"]);
                $where.="and z.title='{$fenqu}' ";
            }
            if(trim($data["sheng"])){
                $sheng=trim($data["sheng"]);
                $where.="and p.title='{$sheng}' ";
            }
            if(trim($data["shi"])){
                $sheng=trim($data["shi"]);
                $where.="and c.title='{$shi}' ";
            }
        }else{
            $data=["no"=>"","jxs"=>"","fenqu"=>"","sheng"=>"","shi"=>""];
        }
        $sum=db("cg_jxs j")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
        ->where($where)->count();
            $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $jarr=db("cg_jxs j")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
        ->where($where)
        ->field("j.title as jxs,z.title as fenqu,p.title as sheng,c.title as shi,j.id as id,j.sn as jsn,j.stuff")->select();
       
        for($i=0;$i<count($jarr);$i++){
            $renyuan=$jarr[$i]["stuff"];
            if($renyuan){
                $renyuan=unserialize($renyuan);
                //print_r($renyuan);
                $tmprenyuan=[];
                foreach ($renyuan as $k => $v) {
                    # code...
                    $vname=db("employee")->where(["id"=>$v])->value("em_name");
                    if($vname){
                        $tmprenyuan[]=$vname;
                    }
                }
                $jarr[$i]["renyuan"]=$tmprenyuan;
            }else{
                $jarr[$i]["renyuan"]=[];
            }
            //未核销
            $weihearr=db::table("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.jxs_id={$jarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0)")->field("pizhunweishui,fb_shijiweishui,hx_yihexiao")->select();
            $jarr[$i]["weihe"]=0;
            foreach ($weihearr as $k => $v) {
                # code...
                if($v["fb_shijiweishui"]){
                    $jarr[$i]["weihe"]+=$v["fb_shijiweishui"];
                }else{
                    $jarr[$i]["weihe"]+=$v["pizhunweishui"];
                }
                if($v["hx_yihexiao"]){
                    $jarr[$i]["weihe"]-=$v["hx_yihexiao"];
                }
            }
            //核销中
            $hx=Db::table("cg_ti_hexiao")->where("status in (1,2,3,4,5,6) and jxs_id={$jarr[$i]["id"]}")->sum("tax");
            if($hx)
                $jarr[$i]["hx"]=round($hx,2);
            else
                $jarr[$i]["hx"]=0;
            //待支付
            $dzf=Db::table("cg_ti_hexiao")->where("status in (7) and jxs_id={$jarr[$i]["id"]}")->sum("tax");
            $zf=Db::table("cg_ti_hexiao")->where("status in (7) and jxs_id={$jarr[$i]["id"]}")->sum("yzfje");
            $jarr[$i]["dzf"]=0;
            if($dzf)
                $jarr[$i]["dzf"]+=$dzf;
            if($zf)
                $jarr[$i]["dzf"]-=$zf;
        }
        $this->assign("jarr",$jarr);
         $this->assign("data", $data);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        return $this->fetch();
    }
}